package sdu.icat.ltp.tool;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import sdu.icat.ltp.service.ApiInService;

public class DataBaseFascade {

    private final static DataBasePool pool = new DataBasePool();
    
    private final static DataBaseFascade _instance = new DataBaseFascade();

    private final static ApiInService _service = new ApiInService();
    
    public static DataBaseFascade newInstance() {
	return _instance;
    }
    
    /**
     * 如果存在返回bookid，若不存在返回-1
     * @param book_title
     * @return
     */
    public static int findBookidByTitle(String book_title) {
	String find_sql = "select id from _new_article_title where article_title="+book_title;
	Connection connection = pool.createConnection();
	Statement stt = null;
	try {
	    stt = connection.createStatement();
	    ResultSet rs = stt.executeQuery(find_sql);
	    if(rs.next()) {
		int id = rs.getInt("id");
		return id;
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    pool.closeConnection(connection);
	}
	return -1;
    }
    
    /**
     * 将数据库中的原文文章发送到ltp平台进行转换
     */
    public static void listArticle() {
	String list_sql = "select a.id,a.chaptertitle,a.paragraphsort,a.paragraphcontent,b.id as bookid from _new_article as a,_new_article_title as b where a.articletitle=b.article_title and a.resolved=0 ";
	Connection connection = pool.createConnection();
	Statement stt = null;
	try {
	    stt = connection.createStatement();
	    ResultSet rs = stt.executeQuery(list_sql);
	    while(rs.next()) {
		int id = rs.getInt("id");
		int bookid = rs.getInt("bookid");
		String chaptertitle = rs.getString("chaptertitle");
		String content = rs.getString("paragraphcontent");
		int sort = rs.getInt("paragraphsort");
		String[] contents = TextBreaker.TextBreak(content);
		for(String read:contents) {
		    _service.saveSimpleArticle("bookid_"+bookid, chaptertitle,sort,read);
		}
		updateRecord(id);
	    }
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    pool.closeConnection(connection);
	}
    }
    
    /**
     * 修改记录
     * @param id
     */
    public static void updateRecord(int id) {
	String update_sql = "update _new_article set resolved=1 where id="+id;
	Connection connection = pool.createConnection();
	Statement stt = null;
	try {
	    stt = connection.createStatement();
	    stt.executeUpdate(update_sql);
	    stt.close();
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    pool.closeConnection(connection);
	}
    }
    
    /**
     * 存储文章
     * 分段存储
     * @param articletitle
     * @param chaptertitle
     * @param paragraphsort
     * @param paragraphcontent
     */
    public static void saveArticle(String articletitle,String chaptertitle,int paragraphsort,String paragraphcontent) {
	String sql = "insert into _new_article(articletitle,chaptertitle,paragraphsort,paragraphcontent) values('"+articletitle+"','"+chaptertitle+"',"+paragraphsort+",'"+paragraphcontent+"')";
	Connection connection = pool.createConnection();
	Statement stt = null;
	try {
	    stt = connection.createStatement();
	    stt.execute(sql);
	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    if(stt!=null) {
		try {
		    stt.close();
		    pool.closeConnection(connection);
		} catch (SQLException e) {
		    e.printStackTrace();
		}
	    }
	}
    }
    
    
    /**
     * 获取数据库所有已录文章标题
     * @return
     */
    public static List<String> getArticleTitles() {
	List<String> titles = new ArrayList<>();
	String list_sql = "SELECT articletitle FROM _new_article GROUP BY articletitle order by id";
	Connection connection = pool.createConnection();
	Statement stt = null;
	try {
	    stt = connection.createStatement();
	    ResultSet rs = stt.executeQuery(list_sql);
	    while(rs.next()) {
		String title = rs.getString("articletitle");
		titles.add(title);
	    }
	    return titles;
	} catch (SQLException e) {
	    e.printStackTrace();
	    return null;
	} finally {
	    pool.closeConnection(connection);
	}
    }
    
    /**
     * 根据标题获取所有章节信息
     * @param title
     * @return
     */
    public static List<String> getChapterlist(String title) {
	List<String> chapter_list = new ArrayList<>();
	String list_sql = "SELECT chaptertitle FROM `_new_article` where articletitle='"+title+"' group by chaptertitle";
	Connection connection = pool.createConnection();
	Statement stt = null;
	try {
	    stt = connection.createStatement();
	    ResultSet rs = stt.executeQuery(list_sql);
	    while(rs.next()) {
		String chaptertitle = rs.getString("chaptertitle");
		chapter_list.add(chaptertitle);
	    }
	    return chapter_list;
	} catch (SQLException e) {
	    e.printStackTrace();
	    return null;
	} finally {
	    pool.closeConnection(connection);
	}
    }
    
    /**
     * 获取文本
     * @param booktitle
     * @param chaptertitle
     * @return
     */
    public static List<Context> getContext(String booktitle,String chaptertitle) {
	List<Context> contexts = new ArrayList<>();
	String book_sql = "select id,paragraphcontent,resolved from _new_article where articletitle='"+booktitle+"' and chaptertitle='"+chaptertitle+"' ORDER BY paragraphsort asc";
	Connection connection = pool.createConnection();
	Statement stt = null;
	try {
	    stt = connection.createStatement();
	    ResultSet rs = stt.executeQuery(book_sql);
	    while(rs.next()) {
		int id = rs.getInt("id");
		String content = rs.getString("paragraphcontent");
		boolean resolved = rs.getBoolean("resolved");
		Context ctxt = new Context();
		ctxt.setId(id);
		ctxt.setResolved(resolved);
		ctxt.setContent(content);
		contexts.add(ctxt);
	    }
	    return contexts;
	} catch (SQLException e) {
	    e.printStackTrace();
	    return null;
	} finally {
	    pool.closeConnection(connection);
	}
	
    }
    
    public static class Context {
	
	private int id;
	
	private String content;
	
	private boolean resolved;

	public int getId() {
	    return id;
	}

	public void setId(int id) {
	    this.id = id;
	}

	public String getContent() {
	    return content;
	}

	public void setContent(String content) {
	    this.content = content;
	}

	public boolean isResolved() {
	    return resolved;
	}

	public void setResolved(boolean resolved) {
	    this.resolved = resolved;
	}
	
    }
    
    public static void main(String[] args) {
	_service.start();
	listArticle();
    }
}
